﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Text;
using System.Web;
using Framework;


namespace Enterprise
{
    public class BusinessCardService : BaseService
    {
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="name"></param>
        /// <param name="nameEn"></param>
        /// <param name="description"></param>
        /// <param name="descriptionEn"></param>
        /// <param name="sort"></param>
        /// <param name="filedata"></param>
        /// <param name="businessCardCategoryId"> </param>
        /// <returns></returns>
        public string[] Add(string name, string nameEn, string description, string descriptionEn, int sort, HttpPostedFileBase filedata = null, int businessCardCategoryId = 0)
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "图片名称");
            Check.Argument.IsNotOutOfLength(nameEn, 50, "图片名称(En)");
            var fileName = string.Empty;
            var thumFileName = string.Empty;
            if (filedata != null && filedata.ContentLength > 0)
            {
                var uploadHelper = new UploadHelper
                                       {
                                           PostedFile = filedata,
                                           RelativePath = "~/Upload/BusinessCard/",
                                           IsAdaptive = true,
                                           IsImage = true,
                                           IsGenerateThumbnail = true,
                                           FileMaxLength = 1024 * 1024 * 2,
                                           ThumbnailHeight = 185,
                                           ThumbnailWidth = 165,
                                           ThumbnailPrefix = "thum"
                                       };
                uploadHelper.Save();
                fileName = uploadHelper.FileName;
                thumFileName = uploadHelper.ThumbnailFileName;
            }
            var sbSql = new StringBuilder();
            sbSql.Append("insert into tbBusinessCard(");
            sbSql.Append("CoNormalImage,CoSmallImage,CoBigImage,CoName,CoNameEn,CoDescription,CoDescriptionEn,CoSort,CoBusinessCardCategoryId,CoCategoryPath");
            sbSql.Append(") values(");
            sbSql.Append("@CoNormalImage,@CoSmallImage,@CoBigImage,@CoName,@CoNameEn,@CoDescription,@CoDescriptionEn,@CoSort,@CoBusinessCardCategoryId,@CoCategoryPath");
            sbSql.Append(")");
            var parameters = new[]
                {
                    new SqlParameter("@CoNormalImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoSmallImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoBigImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoName", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoNameEn", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoDescription", SqlDbType.Text),
                    new SqlParameter("@CoDescriptionEn", SqlDbType.Text),
                    new SqlParameter("@CoSort", SqlDbType.Int),
                    new SqlParameter("@CoBusinessCardCategoryId", SqlDbType.Int),
                    new SqlParameter("@CoCategoryPath", SqlDbType.Text)
                };
            parameters[0].Value = fileName;
            parameters[1].Value = thumFileName;
            parameters[2].Value = fileName;
            parameters[3].Value = name;
            parameters[4].Value = nameEn;
            parameters[5].Value = description;
            parameters[6].Value = descriptionEn;
            parameters[7].Value = sort;
            parameters[8].Value = DBNull.Value;
            parameters[9].Value = DBNull.Value;
            using (var sdr = DbHelperSql.ExecuteReader("select * from tbBusinessCardCategory where CoBusinessCardCategoryId={0}".FormatWith(businessCardCategoryId)))
            {
                if (sdr.Read())
                {
                    parameters[8].Value = businessCardCategoryId;
                    parameters[9].Value = sdr["CoCategoryPath"].SafeToString();
                }
            }
            var rows = DbHelperSql.Add(sbSql.ToString(), parameters).SafeToInt();
            if (rows > 0)
            {
                return new[] { rows.ToString(CultureInfo.InvariantCulture), fileName, thumFileName };
            }
            throw new Exception("添加失败！");
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="nameEn"></param>
        /// <param name="description"></param>
        /// <param name="descriptionEn"></param>
        /// <param name="sort"></param>
        /// <param name="filedata"></param>
        /// <param name="businessCardCategoryId"></param>
        /// <returns></returns>
        public int Eidt(int id, string name, string nameEn, string description, string descriptionEn, int sort, HttpPostedFileBase filedata = null, int businessCardCategoryId = 0)
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "图片名称");
            Check.Argument.IsNotOutOfLength(nameEn, 50, "图片名称(En)");
            SqlParameter[] parameters;
            var sbSql = new StringBuilder();
            sbSql.Append("update tbBusinessCard set ");
            sbSql.Append(" CoName=@CoName,CoDescription=@CoDescription,CoNameEn=@CoNameEn,CoDescriptionEn=@CoDescriptionEn,CoSort=@CoSort,CoBusinessCardCategoryId=@CoBusinessCardCategoryId,CoCategoryPath=@CoCategoryPath");
            if (filedata != null && filedata.ContentLength > 0)
            {
                sbSql.Append(",CoNormalImage=@CoNormalImage,CoSmallImage=@CoSmallImage,CoBigImage=@CoBigImage");
                parameters = new[]
                {
                    new SqlParameter("@CoName", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoDescription", SqlDbType.Text),
                    new SqlParameter("@CoNameEn", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoDescriptionEn", SqlDbType.Text),
                    new SqlParameter("@CoSort", SqlDbType.Int),
                    new SqlParameter("@CoBusinessCardCategoryId", SqlDbType.Int),
                    new SqlParameter("@CoCategoryPath", SqlDbType.Text),
                    new SqlParameter("@CoNormalImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoSmallImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoBigImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@id", SqlDbType.Int)
                };
                parameters[0].Value = name;
                parameters[1].Value = description;
                parameters[2].Value = nameEn;
                parameters[3].Value = descriptionEn;
                parameters[4].Value = sort;
                parameters[5].Value = DBNull.Value;
                parameters[6].Value = DBNull.Value;
                using (var sdr = DbHelperSql.ExecuteReader("select * from tbBusinessCardCategory where CoBusinessCardCategoryId={0}".FormatWith(businessCardCategoryId)))
                {
                    if (sdr.Read())
                    {
                        parameters[5].Value = businessCardCategoryId;
                        parameters[6].Value = sdr["CoCategoryPath"].SafeToString();
                    }
                }
                var uploadHelper = new UploadHelper
                {
                    PostedFile = filedata,
                    RelativePath = "~/Upload/BusinessCard/",
                    IsAdaptive = true,
                    IsImage = true,
                    IsGenerateThumbnail = true,
                    FileMaxLength = 1024 * 1024 * 2,
                    ThumbnailHeight = 185,
                    ThumbnailWidth = 165,
                    ThumbnailPrefix = "thum"
                };
                uploadHelper.Save();
                var fileName = uploadHelper.FileName;
                var thumFileName = uploadHelper.ThumbnailFileName;
                parameters[7].Value = fileName;
                parameters[8].Value = thumFileName;
                parameters[9].Value = fileName;
                parameters[10].Value = id;
                var path = HttpContext.Current.Server.MapPath("~/Upload/BusinessCard/");
                var ds = DbHelperSql.Query("select * from tbBusinessCard where coBusinessCardid={0}".FormatWith(id));
                foreach (DataRow item in ds.Tables["ds"].Rows)
                {
                    FileHelper.DeleteSub(Path.Combine(path, item["CoNormalImage"].SafeToString()));
                    FileHelper.DeleteSub(Path.Combine(path, item["CoSmallImage"].SafeToString()));
                    FileHelper.DeleteSub(Path.Combine(path, item["CoBigImage"].SafeToString()));
                }
            }
            else
            {
                parameters = new[]
                    {
                        new SqlParameter("@CoName", SqlDbType.VarChar, 255),
                        new SqlParameter("@CoDescription", SqlDbType.Text),
                        new SqlParameter("@CoNameEn", SqlDbType.VarChar, 255),
                        new SqlParameter("@CoDescriptionEn", SqlDbType.Text),
                        new SqlParameter("@CoSort", SqlDbType.Int),
                        new SqlParameter("@CoBusinessCardCategoryId", SqlDbType.Int),
                        new SqlParameter("@CoCategoryPath", SqlDbType.Text),
                        new SqlParameter("@id", SqlDbType.Int)
                    };
                parameters[0].Value = name;
                parameters[1].Value = description;
                parameters[2].Value = nameEn;
                parameters[3].Value = descriptionEn;
                parameters[4].Value = sort;
                parameters[5].Value = DBNull.Value;
                parameters[6].Value = DBNull.Value;
                parameters[7].Value = id;
                using (var sdr = DbHelperSql.ExecuteReader("select * from tbBusinessCardCategory where CoBusinessCardCategoryId={0}".FormatWith(businessCardCategoryId)))
                {
                    if (sdr.Read())
                    {
                        parameters[5].Value = businessCardCategoryId;
                        parameters[6].Value = sdr["CoCategoryPath"].SafeToString();
                    }
                }
            }

            sbSql.Append(" where CoBusinessCardId=@id");
            var rows = DbHelperSql.ExecuteSql(sbSql.ToString(), parameters).SafeToInt();
            if (rows > 0)
            {
                return rows;
            }
            throw new Exception("修改失败！");
        }
        public void Delete(int id)
        {
            var path = HttpContext.Current.Server.MapPath("~/Upload/BusinessCard/");
            var ds = DbHelperSql.Query("select * from tbBusinessCard where coBusinessCardid={0}".FormatWith(id));
            foreach (DataRow item in ds.Tables["ds"].Rows)
            {
                FileHelper.DeleteSub(Path.Combine(path, item["CoNormalImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoSmallImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoBigImage"].SafeToString()));
            }
            int rows = DbHelperSql.ExecuteSql("delete  from tbBusinessCard where coBusinessCardid={0}".FormatWith(id));
            if (!(rows > 0))
                throw new Exception("记录不存在！");
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pageValue"></param>
        /// <param name="rowsValue"></param>
        /// <param name="businessCardCategoryId"></param>
        /// <returns></returns>
        public Table GetTable(string name, int pageValue, int rowsValue, int businessCardCategoryId = 0)
        {
            var sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append(" where (CoName like '%'+@CoName+'%' or  CoNameEn like '%'+@CoName+'%') ");
            if (businessCardCategoryId != 0)
            {
                sbSqlWhere.Append(" and CoBusinessCardCategoryId=@CoBusinessCardCategoryId ");
            }
            var parameters = new[]
                           {
                               new SqlParameter("@CoName", SqlDbType.VarChar, 255),
                               new SqlParameter("@CoBusinessCardCategoryId", SqlDbType.Int)
                           };
            parameters[0].Value = name.SqlLike();
            parameters[1].Value = businessCardCategoryId;
            int total = DbHelperSql.GetSingle("select count(*) from TbBusinessCard {0}".FormatWith(sbSqlWhere), parameters).SafeToInt();
            var sqlTwo = new StringBuilder();
            sqlTwo.AppendFormat("select top {0} * from TbBusinessCard {1} ", rowsValue, sbSqlWhere);
            if (pageValue > 1)
                sqlTwo.AppendFormat(" and  coBusinessCardid not in(select top {0} coBusinessCardid {1} order by cosort desc)", rowsValue * (pageValue - 1), sbSqlWhere);
            sqlTwo.Append(" order by cosort desc  ");
            var ds = DbHelperSql.Query(sqlTwo.ToString(), parameters);
            return new Table { Rows = ds.Tables["ds"].Rows, Total = total };
        }
        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            var rows = DbHelperSql.Query("select * from TbBusinessCard where CoBusinessCardId={0}".FormatWith(id)).Tables["ds"].Rows;
            return rows[0];
        }

    }
}
